﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

using System.Data;
using System.Data.SqlClient;
using System.Configuration;

using System.IO;
using NPOI.HSSF.UserModel;
using NPOI.HPSF;


namespace syc.Fc
{
    public partial class JinDu_Report : System.Web.UI.Page
    {
        protected HSSFWorkbook hssfworkbook;
        protected int[] chesi = new int[12];
        protected int[] zhikun = new int[12];
        protected double[] kg = new double[18];

        protected void Page_Load(object sender, EventArgs e)
        {
            if (!this.IsPostBack)
            {
                if ((object)Session["OperatorDID"] != null)
                {
                    if ((int)Session["OperatorDID"] != 0)
                    {
                        this.FenChang.Text = ((int)Session["OperatorDID"]).ToString();
                        this.FenChang.Enabled = false;
                    }
                }
                RiQi.Text = DateTime.Today.AddDays(-1).ToString("yyyy年M月d日");
            }
            this.QueryData();
        }

        protected void InitializeWorkbook()
        {

            FileStream file = new FileStream(HttpContext.Current.Server.MapPath("报表/模板/二分厂生产进度日报表.xls"), FileMode.Open, FileAccess.Read);

            hssfworkbook = new HSSFWorkbook(file);

            DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
            dsi.Company = "陕西延长石油材料有限公司";
            hssfworkbook.DocumentSummaryInformation = dsi;

            SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
            si.Subject = "报表";
            si.Title = "生产进度日报表";
            hssfworkbook.SummaryInformation = si;
        }

        protected void WriteToFile()
        {
            DateTime d;
            if (!DateTime.TryParse(this.RiQi.Text, out d))
            {
                d = DateTime.Today;
            }
            Response.ContentType = "application/ms-excel";
            Response.AddHeader("content-disposition", "attachment; filename=\"" + Server.UrlEncode(this.FenChang.SelectedItem.Text+"进度日报[" + d.ToLongDateString()) + "].xls\"");
            hssfworkbook.Write(Response.OutputStream);
        }


        private void QueryData()
        {
            DateTime d1, d2, d3;
            if (!DateTime.TryParse(RiQi.Text, out d1))
            {
                return;
            }
            SqlConnection Conn = new SqlConnection();
            Conn.ConnectionString = ConfigurationManager.ConnectionStrings["SCMConnectionString1"].ConnectionString;
            Conn.Open();
            SqlCommand Cmd = new SqlCommand();
            Cmd.Connection = Conn;

            Cmd.CommandText =
@"declare @y int
declare @d1 smalldatetime
if exists(select * from 生产月设置 where 分厂=@fc and @d0>=起始日期 and @d0<=截止日期)
begin
	select @y=年度,@d1=起始日期 from 生产月设置 where 分厂=@fc and @d0>=起始日期 and @d0<=截止日期
	if exists(select * from 生产月设置 where 分厂=@fc and 年度=@y and 月份=1)
	begin
		select @d1 as 月累计日期,起始日期 as 年累计日期 from 生产月设置 where 分厂=@fc and 年度=@y and 月份=1
	end
end";
            Cmd.CommandText = Cmd.CommandText.Replace("@d0", "'" + d1.ToShortDateString() + "'");
            Cmd.CommandText = Cmd.CommandText.Replace("@fc", this.FenChang.Text);
            Cmd.CommandType = CommandType.Text;
            SqlDataReader Reader = Cmd.ExecuteReader();
            if (Reader.Read())
            {
                d2 = (DateTime)Reader["月累计日期"];
                d3 = (DateTime)Reader["年累计日期"];
                this.RiQi2.Text = d2.ToLongDateString();
                this.RiQi3.Text = d3.ToLongDateString();
            }
            else
            {
                this.RiQi2.Text = d1.Year.ToString() + "年" + d1.Month.ToString() + "月1日";
                this.RiQi3.Text = d1.Year.ToString() + "年1月1日";
                d2 = DateTime.Parse(this.RiQi2.Text);
                d3 = DateTime.Parse(this.RiQi3.Text);
            }
            Reader.Close();


            //车丝
            Cmd.CommandText = "select * from (select sum(车丝1) as c1,sum(车丝2) as c2,sum(车丝3) as c3,sum(车丝4) as c4,sum(车丝5) as c5,sum(车丝6) as c6 from 生产进度 where 日期='" 
                + d1.ToShortDateString() + "' and 分厂ID=" + FenChang.Text + ") t1,"
                + "(Select sum(车丝1) as c7,sum(车丝2) as c8,sum(车丝3) as c9,sum(车丝4) as c10,sum(车丝5) as c11,sum(车丝6) as c12 from 生产进度 where 日期>='" 
                + d2.ToShortDateString() + "' and 日期<='" + d1.ToShortDateString() + "' and 分厂ID=" + FenChang.Text + ") t2"
                + "";
            Cmd.CommandType = CommandType.Text;
            Reader = Cmd.ExecuteReader();
            if (Reader.Read())
            {
                for (int i = 0; i < 12; i++)
                {
                    if(!Reader.IsDBNull(i))
                        chesi[i] = Reader.GetInt32(i);
                }
            }
            Reader.Close();


            Cmd.CommandText = "select * from (select sum(称测支数) as c1,sum(包装捆数) as c2,sum(入库捆数) as c3 from 生产进度 where 日期='" + d1.ToShortDateString() + "' and 分厂ID=" + FenChang.Text + " and 产品类别ID=1) t1,"
                + "(select sum(称测支数) as c4,sum(包装捆数) as c5,sum(入库捆数) as c6 from 生产进度 where 日期='" + d1.ToShortDateString() + "' and 分厂ID=" + FenChang.Text + " and 产品类别ID=2) t2,"
                + "(select sum(称测支数) as c7,sum(包装捆数) as c8,sum(入库捆数) as c9 from 生产进度 where 日期>='" + d2.ToShortDateString() + "' and 日期<='" + d1.ToShortDateString() + "' and 分厂ID=" + FenChang.Text + " and 产品类别ID=1) t3,"
                + "(select sum(称测支数) as c10,sum(包装捆数) as c11,sum(入库捆数) as c12 from 生产进度 where 日期>='" + d2.ToShortDateString() + "' and 日期<='" + d1.ToShortDateString() + "' and 分厂ID=" + FenChang.Text + " and 产品类别ID=2) t4";
            Reader = Cmd.ExecuteReader();
            if (Reader.Read())
            {
                for (int i = 0; i < 12; i++)
                {
                    if (!Reader.IsDBNull(i))
                        zhikun[i] = Reader.GetInt32(i);
                }
            }
            Reader.Close();

            Cmd.CommandText = "select * from (select sum(称测重量) as c1,sum(包装重量) as c2,sum(入库重量) as c3 from 生产进度 where 日期='" + d1.ToShortDateString() + "' and 分厂ID=" + FenChang.Text + " and 产品类别ID=1) t1,"
                + "(select sum(称测重量) as c4,sum(包装重量) as c5,sum(入库重量) as c6 from 生产进度 where 日期='" + d1.ToShortDateString() + "' and 分厂ID=" + FenChang.Text + " and 产品类别ID=2) t2,"
                + "(select sum(称测重量) as c7,sum(包装重量) as c8,sum(入库重量) as c9 from 生产进度 where 日期>='" + d2.ToShortDateString() + "' and 日期<='" + d1.ToShortDateString() + "' and 分厂ID=" + FenChang.Text + " and 产品类别ID=1) t3,"
                + "(select sum(称测重量) as c10,sum(包装重量) as c11,sum(入库重量) as c12 from 生产进度 where 日期>='" + d2.ToShortDateString() + "' and 日期<='" + d1.ToShortDateString() + "' and 分厂ID=" + FenChang.Text + " and 产品类别ID=2) t4,"
                + "(select sum(称测重量) as c13,sum(包装重量) as c14,sum(入库重量) as c15 from 生产进度 where 日期>='" + d3.ToShortDateString() + "' and 日期<='" + d1.ToShortDateString() + "' and 分厂ID=" + FenChang.Text + " and 产品类别ID=1) t5,"
                +"(select sum(称测重量) as c16,sum(包装重量) as c17,sum(入库重量) as c18 from 生产进度 where 日期>='" + d3.ToShortDateString() + "' and 日期<='" + d1.ToShortDateString() + "' and 分厂ID=" + FenChang.Text + " and 产品类别ID=2) t6";
            Reader = Cmd.ExecuteReader();
            if (Reader.Read())
            {
                for (int i = 0; i < 18; i++)
                {
                    if (!Reader.IsDBNull(i))
                        kg[i] = Reader.GetDouble(i);
                }
            }
            Reader.Close();

            Conn.Close();
            Reader.Dispose();
            Cmd.Dispose();
            Conn.Dispose();
        }

        protected void Button_Export_Click(object sender, EventArgs e)
        {
            InitializeWorkbook();
            HSSFSheet sheet1 = hssfworkbook.GetSheet("报表");

            sheet1.GetRow(1).GetCell(2).SetCellValue(this.FenChang.SelectedItem.Text);
            sheet1.GetRow(1).GetCell(10).SetCellValue(this.RiQi.Text);

            this.QueryData();

            int i;
            for(i=0;i<6;i++)
            {
                sheet1.GetRow(3).GetCell(i*2+2).SetCellValue(chesi[i]);
                sheet1.GetRow(4).GetCell(i * 2 + 2).SetCellValue(chesi[i+6]);

                sheet1.GetRow(i + 6).GetCell(3).SetCellValue(zhikun[i]);
                sheet1.GetRow(i + 6).GetCell(4).SetCellValue(zhikun[i + 6]);

                sheet1.GetRow(i + 6).GetCell(7).SetCellValue(kg[i]);
                sheet1.GetRow(i + 6).GetCell(8).SetCellValue(kg[i + 6]);
                sheet1.GetRow(i + 6).GetCell(10).SetCellValue(kg[i + 12]);
            }

            string op = (string)this.Session["OperatorName"];
            if (op == null)
            {
                op = "未知用户";
            }
            sheet1.GetRow(12).GetCell(12).SetCellValue(op);

            sheet1.ForceFormulaRecalculation = true;
            WriteToFile();
            Response.End();
        }

    }
}
